﻿using Common;
using Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using Dapper;

namespace DbControl
{
    public  class OprParameter
    {

        public static List<parameters> GetParameterAll()
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"SELECT id 
                          ,parameter_code 
                          ,parameter_name 
                          ,parameter_unit 
                          ,parameter_datatype 
                          ,parameter_point 
                          ,parameter_attribute 
                          ,parameter_type 
                          ,flag 
                      FROM parameters WHERE flag>0 ";
                    return connection.Query<parameters>(sql).ToList();
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return null;
            }
        }
        public static DataTable GetParameterUse()
        {
            DataTable dt = new DataTable();
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"SELECT id 
                                  ,parameter_code 
                                  ,parameter_name 
                                  ,parameter_unit 
                                  ,parameter_datatype 
                                  ,parameter_point 
                                  ,parameter_attribute 
                                  ,parameter_type 
                                  ,flag 
                              FROM parameters where  flag>0";
                    dt.Load(connection.ExecuteReader(sql));
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog("加载参数列表出错!" + ex.Message.ToString());
                dt = null;
            }
            return dt;
        }
        public static DataTable GetParameterByName(string Name)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = "";
                if (string.IsNullOrEmpty(Name))
                {
                    sql = @"SELECT id 
                          ,parameter_code 
                          ,parameter_name 
                          ,parameter_unit 
                          ,parameter_datatype 
                          ,parameter_point 
                          ,parameter_attribute 
                          ,parameter_type 
                          ,flag 
                      FROM parameters ";
                }
                else
                {
                    sql = string.Format(@"SELECT id 
                          ,parameter_code 
                          ,parameter_name 
                          ,parameter_unit 
                          ,parameter_datatype 
                          ,parameter_point 
                          ,parameter_attribute 
                          ,parameter_type 
                          ,flag 
                      FROM parameters WHERE parameter_name='{0}'", Name);
                }
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        public static parameters GetParameterById(int id)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT id 
                          ,parameter_code 
                          ,parameter_name 
                          ,parameter_unit 
                          ,parameter_datatype 
                          ,parameter_point 
                          ,parameter_attribute 
                          ,parameter_type 
                          ,flag 
                      FROM parameters  WHERE   id=@id";
                return connection.Query<parameters>(sql, new { id = id }).SingleOrDefault();
            }
        }
        public static bool IsExistParameterCode(string parameter_code)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"select COUNT(id)  from parameters WHERE  parameter_code =@parameter_code ";
                    intNum = (int)connection.ExecuteScalar(sql, new { parameter_code = parameter_code });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static bool IsExistParameterName(string parameter_name)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"select COUNT(id)  from parameters WHERE  parameter_name =@parameter_name ";
                    intNum = (int)connection.ExecuteScalar(sql, new { parameter_name = parameter_name });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static int DeleteById(int id)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"DELETE FROM parameters WHERE id=@id";
                    sql = @"UPDATE parameters SET flag = 0 WHERE id=@id";
                    return connection.Execute(sql, new { id = id });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int Update(parameters dv)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"UPDATE  parameters
                                       SET parameter_code = @parameter_code
                                          ,parameter_name = @parameter_name
                                          ,parameter_unit = @parameter_unit
                                          ,parameter_datatype = @parameter_datatype
                                          ,parameter_point = @parameter_point
                                          ,parameter_attribute = @parameter_attribute
                                          ,parameter_type = @parameter_type
                                          ,flag = @flag
                                     WHERE  id=@id";
                    return connection.Execute(sql, dv);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int Insert(parameters dv)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"INSERT INTO parameters
                                   (parameter_code 
                                   ,parameter_name 
                                   ,parameter_unit 
                                   ,parameter_datatype 
                                   ,parameter_point 
                                   ,parameter_attribute 
                                   ,parameter_type 
                                   ,flag)
                             VALUES
                                   (@parameter_code
                                   ,@parameter_name
                                   ,@parameter_unit
                                   ,@parameter_datatype
                                   ,@parameter_point
                                   ,@parameter_attribute
                                   ,@parameter_type
                                   ,@flag)";
                    return connection.Execute(sql, dv);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
    }
}
